﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Web.Services;
using System.Data;
using System.Collections;
using System.IO;

namespace BSC_SERVICES
{
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [System.ComponentModel.ToolboxItem(false)]

    public class Service1 : System.Web.Services.WebService
    {
        #region khai bao bien
        BSC_SERVICES.Code.Connect_DataBase _db = new Code.Connect_DataBase();
        BSC_SERVICES.Code.Login _login = new Code.Login();
        BSC_SERVICES.Code.CL_Ultility _cl = new Code.CL_Ultility();
        BSC_SERVICES.Code.Common _cm = new Code.Common();
        BSC_SERVICES.Code.ImportData _imp = new Code.ImportData();
        BSC_SERVICES.Code.ReadErrorDesc _readError = new Code.ReadErrorDesc();
        #endregion

        #region "HAM GHI LOG, error "

        [WebMethod]
        public void UPDATE_LOG_FILE(string pv_logtype, string PV_TLID, string p_Sukien, string p_hamgoi, string Err_str)
        {
            _cl.UPDATE_LOG_FILE(pv_logtype, PV_TLID, p_Sukien, p_hamgoi, Err_str);

        }

        [WebMethod]
        public void WritetoTLLOG(string _nguoitao, string _nguoiduyet, string _magiaodich, string _ngaytao, string _ngayduyet,
            string _thoigiantao, string _thoigianduyet, string _duyet, string _tennghiepvu, string _sochungtu, string _motathem)
        {
            _db.WritetoTLLOG(_nguoitao, _nguoiduyet, _magiaodich, _ngaytao,_ngayduyet, _thoigiantao, _thoigianduyet, _duyet, _tennghiepvu, _sochungtu, _motathem);
        }

        [WebMethod]
        public string Get_error_by_message(string _message, string _tableName)
        {
            return _readError.Get_error_by_message(_message, _tableName);
        }

        #endregion 

        #region hàm linh tinh

        // truoc khi update, cap nhap vao maintainlog 
        [WebMethod]
        public string WriteToMaintain_log(string _fldname, DataTable _tableValue, DataTable _OldTableValue, string _ID_ColumnName, string _ID, string _maker_ID)
        {
            return this._db.WriteToMaintain_log(_fldname, _tableValue, _OldTableValue,_ID_ColumnName, _ID, _maker_ID);
        }

        // sau khi insert vao maintainlog --> update vao du lieu that
        [WebMethod]
        public string UpdateDataReal(string _fldname , string _ID_ColumnName, string _ID, string _maker_ID)
        {
            return this._db.UpdateDataReal(_fldname, _ID_ColumnName, _ID, _maker_ID);
        }

        //lay du lieu cho bang duyet sua tu bang maintain_log
        [WebMethod]
        public DataTable GetTableData_DuyetSua(string _record_key, string _formName)
        {
            string _sql = "SELECT tl.tlname maker_name,log.maker_dt,fs.caption column_name,"+
                                 "(case when (select count (*) from allcode where cdname = log.column_name and cdtype = '"+_formName+"') >0 "+
                                       "then (select ac.cdcontent from allcode ac where ac.cdname = log.column_name and ac.cdtype = '"+_formName+"' and log.from_value = ac.cdval) "+
                                       "else log.from_value "+
                                       "end) as from_value, "+
                                 "(case when (select count (*) from allcode where cdname = log.column_name and cdtype = '" + _formName + "') >0 " +
                                       "then (select ac.cdcontent from allcode ac where ac.cdname = log.column_name and ac.cdtype = '" + _formName + "' and log.to_value = ac.cdval) " +
                                       "else log.to_value "+
                                       "end) as to_value, "+
                                 "log.temp_image  " +
                           "FROM tlprofiles tl,bsc_maintain_log log " +
                           "inner join fldmaster fs ON fs.fldname = log.column_name and fs.objname = '" + _formName + "' " +
                           "where record_key = '" + _record_key + "' " +
                                  "and tl.tlid = log.maker_id " +
                                  "and log.approve_time is null " +
                                  "and ( (case when log.from_value is NULL then log.to_value " +
                                                      "when log.from_value <> log.to_value then log.to_value " +
                                                      "else null end ) is not null " +
                                           "or log.temp_image is not null )";
            return GetDatatable_BySQL(_sql);
        }

        [WebMethod]
        public string HelloWorld()
        {
            _cl.CREATE_LOG_FILE("Log : ");
            return System.Web.Hosting.HostingEnvironment.ApplicationPhysicalPath;
        }

        [WebMethod]
        public bool CheckLogin(string _username, string _password)
        {
            return _login.CheckLogin(_username, _password);
        }

        // Lấy toàn bộ menu left
        [WebMethod]
        public DataTable GetMenuLeft()
        {
            DataTable _result = _db.Return_Datatable("SELECT * FROM BSC_TBL_MENU ORDER BY MENUCODE");
            _result.TableName = "TBL_MENU";
            return _result;
        }

        //Lấy menu theo nhóm quyền
        [WebMethod]
        public DataTable GetMenu_ByGroupsPermission(List<string> _e)
        {
            DataTable _result = new DataTable("bsc_group_permission");
            string sql = "select * from bsc_group_permission where ";
            foreach (var item in _e)
            {
                if (_e.IndexOf(item) == 0)
                {
                sql += " grpid like '" + item + "'";    
                }
                else
                {
                    sql += " or grpid like '" + item + "'";
                }
            }
            _result = _db.Return_Datatable(sql);
            return _result;
        }

        [WebMethod]
        public DataTable GetMenu_ByGroupPermission(string _e)
        {
            DataTable _result = new DataTable("bsc_group_permission");
            _result = _db.Return_Datatable("select * from bsc_group_permission where grpid like '" + _e + "'");
            return _result;
        }

        // Lấy menu theo menuid
        [WebMethod]
        public DataTable GetMenu_ByMenuID(string _e)
        {
            DataTable _result = _db.Return_Datatable("select * from bsc_tbl_menu where menuid like '" + _e + "'");
            _result.TableName = "bsc_tbl_menu";
            return _result;
        }

        // Lấy menu theo menuCode
        [WebMethod]
        public DataTable GetMenu_ByMenuCode(string _e)
        {
            DataTable _result = _db.Return_Datatable("select * from bsc_tbl_menu where menucode like '" + _e + "'");
            _result.TableName = "bsc_tbl_menu";
            return _result;
        }

        // Lấy menu theo menuname
        [WebMethod]
        public DataTable GetMenu_ByMenuName(string _e)
        {
            DataTable _result = _db.Return_Datatable("select * from bsc_tbl_menu where menuname like '" + _e + "'");
            _result.TableName = "bsc_tbl_menu";
            return _result;
        }

        // Lay thong tin quyen them sua xoa theo GRPID + MENUID
        [WebMethod]
        public string GetAuthCode_ByGrpid_Menuid(List<string> _grpid, string _menuID)
        {
            DataTable _table = _db.Return_Datatable("select * from bsc_group_permission where grpid like '" + _grpid + "' and menuid='" + _menuID + "'");
            _table.TableName = "bsc_group_permission";
            if (_table.Rows.Count > 0)
            {
                return _table.Rows[0]["authcode"].ToString();
            }
            return null;
        }

        // LAY_THONG_TIN_LOI_TU_MA_LOI
        [WebMethod]
        public string LAY_THONG_TIN_LOI_TU_MA_LOI(string _e)
        {
            //return _cl.LAY_THONG_TIN_LOI_TU_MA_LOI(_e);
            return "";
        }

        // Excute Store Oracle by List String
        [WebMethod]
        public DataTable Excute_Store_By_ListString(string _storeName, DataTable _value)
        {
            DataTable _result = _db.Execute_Store_By_ListString(_storeName, _value);
            _result.TableName = _storeName;
            return _result;
        }

        //Get Danh Sach Bao Cao
        [WebMethod]
        public DataTable GetListReports(string _storeName)
        {
            DataTable _result = _db.Return_Datatable("select modcode,rptid,description from bsc_tbl_rptmaster where modcode ='" + _storeName + "' and visible ='Y' order by rptid asc");
            _result.TableName = _storeName;
            return _result;
        }

        /// <summary>
        /// DATNT: Hàm thưc thi giao dịch
        /// </summary>
        /// <param name="_e"></param>
        /// <returns></returns>
        [WebMethod]
        public string Excute_Store_Transaction(string _storeName, DataTable _value)
        {
            string _result = _db.Execute_Store(_storeName, _value);
            
            return _result;
        }

        [WebMethod]
        public bool Check_TrungGiaTri(string _value, string _tableName, string _columnName)
        {
            return _db.Check_TrungGiaTri(_value, _tableName, _columnName);
        }
        #endregion

        #region Get View

        // trả về các tât cả giao dịch 
        [WebMethod]
        public DataTable Get_All_GiaoDich(string _sql)
        {
            DataTable _result = _db.Return_Datatable("select a.autoid,a.ngaytaogd,a.makh,a.maso,a.tennghiepvu,a.sotien_soluong,a.duyet, "+
                                                            "(case when a.nguoiduyet is null then a.nguoiduyet else (select tlname from tlprofiles where tlid = a.nguoiduyet) end) as nguoiduyet, " +
                                                            "a.magiaodich,a.diengiai,a.ngayduyetgd,a.giotaogd,tl.tlname nguoitaogd, b.menuid " +
                                                     "from bsc_tbl_tllogall a, bsc_tbl_menu b,tlprofiles tl " +
                                                     "where a.magiaodich = b.menucode " +
                                                     "and tl.tlid = a.nguoitaogd " + _sql);
            _result.TableName = "TblGiaoDich";
            return _result;
        }
        
        [WebMethod]
        public DataTable CheckPermission_View_ByMenuCode(string _grpid, string _menucode)
        {
            DataTable _result = _db.Return_Datatable("select * from bsc_group_permission where menuid=( " +
                                                        "select menuid from bsc_tbl_menu where menucode like '" + _menucode + "') " +
                                                      "and grpid='" + _grpid + "'");
            _result.TableName = "bsc_group_permission";
            return _result;
        }

        [WebMethod]
        public DataTable GetViewBy_SearchCode(string _searchcode)
        {
            //DataTable _result = _db.Return_Datatable("select * from searchfld" +
            //                            " where searchcode like '" + _searchcode + "'" +
            //                            " order by position");
            DataTable _result = _db.Return_Datatable("select * from fldmaster" +
                                        " where objname like '" + _searchcode + "'" +
                                        " order by odrnum");
            _result.TableName = "searchfld";
            return _result;
        }

        [WebMethod]
        public DataTable SearchView_ByParameters(string _tablename, DataTable _table)
        {
            DataTable _dt_sqlSelect = _db.SearchView_BySQL("select * from search where searchcode like '" + _tablename + "'");
            DataTable _result;
            if (_dt_sqlSelect.Rows.Count == 0)
            {
                return null;
            }
            else
            {
                string sql_Select = _dt_sqlSelect.Rows[0]["searchcmdsql"].ToString() ;
                _result = _db.SearchView_ByParameters(sql_Select, _table);
            }
            _result.TableName = "noname";
            return _result; 
        }
        
        [WebMethod]
        public DataTable SearchView_BySQL(string _tablename, string _sql)
        {
            DataTable _dt_sqlSelect = _db.SearchView_BySQL("select * from search where searchcode like '" + _tablename + "'");
            DataTable _result;
            if (_dt_sqlSelect.Rows.Count == 0)
            {
                return null;
            }
            else
            {
                string sql_Select = _dt_sqlSelect.Rows[0]["searchcmdsql"].ToString() + _sql;
                _result = _db.SearchView_BySQL(sql_Select);
            }
            _result.TableName = _tablename;
            return _result; 
        }

        [WebMethod]
        public DataTable SearchView_BySearchCode(string v_strSearchCode, string _sql, string v_tlid)
        {
            DataTable _result = _db.SearchView_BySearhCode(v_strSearchCode,_sql,v_tlid);
            _result.TableName = "CUONGTHI";
            return _result;
        }

        [WebMethod]
        public DataTable GetFldMasterBy_Objname(string _objName)
        {
            DataTable _result = _db.Return_Datatable("select * from fldmaster" +
                                        " where objname like '" + _objName + "' and visible like 'Y' " +
                                        " order by odrnum");
            _result.TableName = "fldmaster";
            return _result;
        }

        [WebMethod]
        public string GetID_ByCombobox(string _sql, string _value)
        {
            DataTable _result = _db.GetID_ByCombobox(_sql,_value);
            _result.TableName = "noname";
            if (_result.Rows.Count>0)
            {
                return _result.Rows[0]["value"].ToString();
            }
            return "";
        }

        [WebMethod]
        public DataTable GetDatatable_BySQL(string _sql)
        {
            DataTable _result = _db.Return_Datatable(_sql);
            _result.TableName = "noname";
            return _result;
        }

        [WebMethod]
        public DataTable GetDatatable_Where(string tableName,string columnName, string value)
        {
            string _sql = "Select * from " + tableName + " where " + columnName + " = '" + value + "'";
            DataTable _result = _db.Return_Datatable(_sql);
            _result.TableName = "noname";
            return _result;
        }

        [WebMethod]
        public DataTable GetPhepToanBy_SearchCode(string _searchcode)
        {
            DataTable _result = _db.Return_Datatable("select * from searchfld" +
                                        " where searchcode like '" + _searchcode + "'" +
                                        " AND  srch like 'Y'" +
                                        " order by position");
            _result.TableName = "searchfld";
            return _result;
        }

        [WebMethod]
        public DataTable GetData_AllGroups()
        {
            //DataTable _result = _db.Return_Datatable("select * from allcode where cdname=\'TLGROUP\'");
            DataTable _result = _db.Return_Datatable("select * from tlgroups where grpname like '%N N%' "  +
                                                        "or grpname like '%nhóm IT%' " +
                                                        "or grpname like '%Nhóm IT%' " + 
                                                        "and active like 'Y'");
            _result.TableName = "tlgroups";
            return _result;
        }

        [WebMethod]
        public DataTable Get_UsersByGroup(string _e)
        {
            //DataTable _result = _db.Return_Datatable("select tlid, tlname,tlfullname,active from tlprofiles where tlgroup like '" + _e + "'");
            DataTable _result = _db.Return_Datatable("Select c.* From tlgroups a , tlgrpusers b, tlprofiles c "+
                                                        "where a.grpid = b.grpid "+
                                                        "and b.tlid = c.tlid "+
                                                        "and a.grpid ='" + _e + "'");
            _result.TableName = "tlprofiles";
            return _result;
        }

        [WebMethod]
        public string Get_UserID_ByUserName(string _e)
        {
            DataTable _result = _login.Return_Datatable("select tlid from tlprofiles where TLNAME like '" + _e + "'");
            _result.TableName = "tlprofiles";
            return _result.Rows[0][0].ToString();
        }

        [WebMethod]
        public DataTable Get_Permission_ByAutoID(string _e)
        {
            DataTable _result = _db.Return_Datatable("select * from bsc_group_permission where autoid like '" + _e + "'");
            _result.TableName = "bsc_group_permission";
            return _result;
        }

        [WebMethod]
        public DataTable Get_Group_ByUserName(string _e)
        {
            //DataTable _result = _db.Return_Datatable("select * from allcode where cdval=( select tlgroup from tlprofiles where  tlname like '" + _e.ToUpper() + "') and cdname like 'TLGROUP'");
            DataTable _result = _login.Return_Datatable("select * from tlgroups where grpid in ( select grpid from tlgrpusers where tlid=( select tlid from tlprofiles where  tlname like '" + _e.ToUpper() + "')) " +
                                                        "and active='Y' " +
                                                        "and (grpname like '%N N%' " +
                                                        "or grpname like '%nhóm IT%' "+
                                                        "or grpname like '%Nhóm IT%')");
            _result.TableName = "tlgroups";
            return _result;
        }

        [WebMethod]
        public DataTable Get_Table_SoTraiChu()
        {
            DataTable _result = _db.Return_Datatable("select * from bsc_tbl_sotraichu");
            _result.TableName = "bsc_tbl_sotraichu";
            return _result;
        }

        [WebMethod]
        public DataTable Get_Table_SoTraiChu_By_ID(string _e)
        {
            DataTable _result = _db.Return_Datatable("select * from bsc_tbl_sotraichu where AUTOID like '"+_e+"'");
            _result.TableName = "bsc_tbl_sotraichu";
            return _result;
        }

        [WebMethod]
        public DataTable Get_Table_TTTraLaiTP()
        {
            DataTable _result = _db.Return_Datatable("select * from bsc_tbl_coupon");
            _result.TableName = "bsc_tbl_sotraichu";
            return _result;
        }

        [WebMethod]
        public DataTable Get_Table_TPPH_BY_NgayTTlai(string datetime)
        {
            DataTable _result = _db.Return_Datatable("select DISTINCT (tp.matp), tp.tentp from bsc_tbl_tpph tp, bsc_tbl_coupon cp " +
                                                      "where tp.matp = cp.matp and cp.ngayttlai = TO_DATE('" + datetime + "','dd/MM/yyyy')");
            _result.TableName = "bsc_tbl_tpph";
            return _result;
        }

        [WebMethod]
        public DataTable Get_Table_TPPH_BY_ID(string _ID)
        {
            DataTable _result = _db.Return_Datatable("select * from bsc_tbl_tpph where MATP='"+_ID+"'");
            _result.TableName = "bsc_tbl_tpph";
            return _result;
        }

        [WebMethod]
        public DataTable Get_List_Sotraichu_BY_NgayTTlai(string _tp, string _macoupon, string _dotgopvon )
        {
            string sql = "";            
            if (_dotgopvon == "6")// "ALL"
                sql = "Select st.maso, st.makh, st.matp, cp.macoupon, cp.thanhtoangoc from bsc_tbl_coupon cp, bsc_tbl_sotraichu st " +
                      "Where cp.matp = st.matp and cp.trangthai = '0' and cp.matp = '" + _tp + "' " + "and cp.macoupon = '" + _macoupon + "'" +
                             "and st.maso not in (Select maso From bsc_tbl_thanhtoancoupon Where macoupon = cp.macoupon) ";
            else              
            sql = "Select st.maso, st.makh, st.matp, cp.macoupon, cp.thanhtoangoc from bsc_tbl_coupon cp, bsc_tbl_sotraichu st " +
                   "Where cp.matp = st.matp and cp.dotgopvon = st.dotgopvon and cp.trangthai = '0' and cp.matp = '" + _tp + "' "+ "and cp.macoupon = '" + _macoupon + "'" +
                          "and st.maso not in (Select maso From bsc_tbl_thanhtoancoupon Where macoupon = cp.macoupon) ";
            DataTable _result = _db.Return_Datatable(sql);
            _result.TableName = "bsc_tbl_tpph";
            return _result;
        }

        [WebMethod]
        public bool Update_Coupon_Status(string _macoupon, int _trangthai)
        {
            string sql = "";
            sql = "UPDATE bsc_tbl_coupon cp SET cp.trangthai = '" + _trangthai  + "' WHERE cp.macoupon = '" + _macoupon + "'";
            return Excute_DB(sql);
            
        }

        [WebMethod]
        public DataTable Get_Table_Coupon(string _e)
        {
            DataTable _result = _db.Return_Datatable("select * from bsc_tbl_coupon");
            _result.TableName = "bsc_tbl_coupon";
            return _result;
        }

        /// <summary>
        /// Get date for Coupon table
        /// </summary>
        /// <param name="MATP">MATP</param>
        /// <returns>DataTable</returns>
        /*[WebMethod]
        public DataTable Get_Table_Coupon_By_MaTP(string _e)
        {
            DataTable _result = _db.Return_Datatable("select * from bsc_tbl_coupon where matp = '" + _e + "'");
            _result.TableName = "bsc_tbl_coupon";
            return _result;
        }*/

        [WebMethod]
        public DataTable Get_Table_Coupon_By_Matp(string _e)
        {
            DataTable _result = _db.Return_Datatable("select * from bsc_tbl_coupon where matp like '"+_e+"'");
            _result.TableName = "bsc_tbl_coupon";
            return _result;
        }

        [WebMethod]
        public DataTable Get_Table_Coupon_By_Macoupon(string _e)
        {
            DataTable _result = _db.Return_Datatable("select * from bsc_tbl_coupon where macoupon like '" + _e + "'");
            _result.TableName = "bsc_tbl_coupon";
            return _result;
        }

        #endregion

        #region them, sua, xoa

        [WebMethod]
        public string AddNewRow(string _fldname, DataTable _tableValue)
        {
            return _db.AddNewRow(_fldname, _tableValue);
        }

        [WebMethod]
        public string AddNewRow_ReturnID(string _fldname, DataTable _tableValue)
        {
            return _db.AddNewRowReturnID(_fldname, _tableValue);
        }

        [WebMethod]
        public string EditRow(string _fldname, DataTable _tableValue, string _ID_ColumnName, string _ID, string _UserID)
        {
            return _db.EditRow(_fldname, _tableValue, _ID_ColumnName, _ID, _UserID);
        }

        /*[WebMethod]
        public string DeleteRow(string _formName, string _ID, string _ID_ColumnName, string _UserID)
        {
            return _db.DeleteRow(_formName, _ID, _ID_ColumnName, _UserID);
        }*/

        [WebMethod]
        public string DeleteRow(string _formName, string _ID, string _ID_ColumnName, string _UserID)
        {
            string sql = "update " + _formName + " set invalid = 'Y', duyet = '4'"
                                   + " where " + _ID_ColumnName + " = '" + _ID + "'";
            bool b = _db.Excute_DB(sql);
            if (b)
                return "OK";
            else
                return "Error";
        }

        [WebMethod]
        public bool Excute_DB(string _sql)
        {
            return _db.Excute_DB(_sql);
        }

        #endregion

        #region up mau so

        [WebMethod]
        public byte[] GetDocument(string _tenMauSoTC)
        {
            return _cl.GetDocument(_tenMauSoTC);
        }

        [WebMethod]
        public string SaveDocument(string _tenMauSoTC, byte[] _data)
        {
            try
            {
                _cl.SaveDocument(_tenMauSoTC, _data);
                return "0";
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }

        // update ten mau so TP
        [WebMethod]
        public string UpdateTenMauSoTP(string _matp, string _filename)
        {
            return _db.UpdateTenMauSoTP(_matp, _filename);
        }

        #endregion

        #region checker/maker
        /// <summary>
        /// auther: DatNT
        /// Hàm duyệt dữ liệu thông tin
        /// </summary>
        /// <param name="_acc">Tên nhân viên thực hiện</param>
        /// <param name="_ColumnName">Tên trường</param>
        /// <param name="_id">Giá trị của trường</param>
        /// <param name="_formName">Tên bảng thao tác</param>
        /// <returns>true/alse</returns>
        /// 
        [WebMethod]
        public bool Approve_Data(string _formName, string _ColumnName, string _id, int _duyet)
        {
            bool b = false;
            string sql = "update " + _formName + " set duyet = '"+ _duyet +"' where " + _ColumnName + " = " + _id;
            b = Excute_DB(sql);
            return b;
        }

        [WebMethod]
        ///Check trường dl đã duyệt chưa 0:chưa duyệt / 1: đã duyệt?
        public int CheckApprove(string _formName, string _ColumnName, string _id)
        {
            return _db.GetApproved(_formName, _ColumnName, _id);
        }


        //Chen du lieu giao dich vao bang duyet giao dich
        [WebMethod]
        public string AddTransData(DataTable _tbl)
        {
            string result = _db.AddNewRowReturnID("BSC_TBL_TLLOGALL", _tbl);
            return result;
        }

        //update thong tin nguoi duyet vao bang duyet giao dich
        [WebMethod]
        public bool AddTransApprove(string _userID, string _id)
        {
             return _db.Excute_DB("update bsc_tbl_tllogall set ngayduyetgd='" + DateTime.Now.ToString() + "', " +
                                                           "nguoiduyet ='" + _userID + "' " +
                                                           "where autoid='" + _id + "'");
        }
        #endregion

        #region Import danh sach
        [WebMethod]
        public DataTable Import_TableData(string _formName, DataTable _tbl, string userID, string menu_Code, string duyet, string _function)
        {
            //insert du lieu 
            DataTable result = _imp.Import_TableData(_formName, _tbl, userID, menu_Code, duyet, _function);
            //cap nhat thong tin nguoi them
            _imp.Update_Table_Last_Change(_formName, userID, result);
            return result;
        }

        //import danh sách chữ ký
        [WebMethod]
        public bool Import_Signals(DataTable _tbl, string userID, string menu_Code, string duyet, string _function)
        {
            return _imp.Import_Signals(_tbl, userID, menu_Code, duyet, _function);
        }
        #endregion


        #region Checksoluongtraiphieu
        [WebMethod]
        public double Get_SL_TPPH(string _matp)
        {
            DataTable _result = _db.Return_Datatable("select soluong from bsc_tbl_tpph where matp = '" + _matp + "'");
            _result.TableName = "bsc_tbl_tpph";
            if (_result.Rows.Count <= 0)
                return 0;
            else
                return Convert.ToDouble(_result.Rows[0][0]);
        }

        [WebMethod]
        public double Get_ALL_TP(string _matp)
        {
            DataTable _result = _db.Return_Datatable("select sum(slphothong+slhanche+slcamco) from bsc_tbl_sotraichu where matp = '" + _matp + "' group by matp");
            _result.TableName = "bsc_tbl_sotraichu";
            if (_result.Rows.Count <= 0)
                return 0;
            else
                return Convert.ToDouble(_result.Rows[0][0]);
        }

        [WebMethod]
        public double Get_ALL_TP_Where(string _matp, string _id)
        {
            DataTable _result = _db.Return_Datatable("select sum(slphothong+slhanche+slcamco) from bsc_tbl_sotraichu where matp = '" + _matp + "' and autoid <> '" +_id+ "' group by matp");
            _result.TableName = "bsc_tbl_sotraichu";
            if (_result.Rows.Count > 0)
                return Convert.ToDouble(_result.Rows[0][0]);
            else
                return 0;
        }
        #endregion

        #region luuvet
        [WebMethod]
        public bool Update_Last_Change(string _formName, string _id, string _userID)
        {
            string primarykey = _db.PrimaryKey(_formName);
            bool b = Excute_DB("update " +_formName+ " set last_changeid = '"+ _userID+"',"
                                         + " last_change = TO_DATE('" + _cm.Convert_String2DateTime(DateTime.Now.ToString(), "dd/mm/yyyy hh:pp:ss") + "','dd/mm/yyyy hh24:mi:ss')"
                                         + " where "+primarykey+ " = '" +_id + "'" );
            if (!b)
                return false;
            else
                return true;
        }

        [WebMethod]
        public bool Update_Last_Change_By_ColumnName(string _formName, string _colName, string _colId, string _userID)
        {
            string primarykey = _colName;
            bool b = Excute_DB("update " + _formName + " set last_changeid = '" + _userID + "',"
                                         + " last_change = TO_DATE('" + _cm.Convert_String2DateTime(DateTime.Now.ToString(), "dd/mm/yyyy hh:pp:ss") + "','dd/mm/yyyy hh24:mi:ss')"
                                         + " where " + primarykey + " = '" + _colId + "'");
            if (!b)
                return false;
            else
                return true;
        }

        [WebMethod]
        public bool Update_Last_Approve(string _formName, string _columnName, string _id, string _userID)
        {
            bool b = Excute_DB("update " + _formName + " set last_approveid = '" + _userID + "',"
                                         + " last_approve = TO_DATE('" + _cm.Convert_String2DateTime(DateTime.Now.ToString(), "dd/mm/yyyy hh:pp:ss") + "','dd/mm/yyyy hh24:mi:ss')"
                                         + " where " + _columnName + " = '" + _id + "'");
            if (!b)
                return false;
            else
                return true;
        }

        #endregion
    }
}
